﻿using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using iTextSharp.text;
using iTextSharp.text.pdf;

namespace Texfina.Core.Web
{
   public class ExcelHelper
   {
      //Row limits older excel verion per sheet, the row limit for excel 2003 is 65536

      const int rowLimit = 65000;

      private static string getWorkbookTemplate()
      {
         var sb = new StringBuilder(818);
         sb.AppendFormat(@"<?xml version=""1.0""?>{0}", Environment.NewLine);
         sb.AppendFormat(@"<?mso-application progid=""Excel.Sheet""?>{0}", Environment.NewLine);
         sb.AppendFormat(@"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
         sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine);
         sb.AppendFormat(@" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine);
         sb.AppendFormat(@" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
         sb.AppendFormat(@" xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}", Environment.NewLine);
         sb.AppendFormat(@" <Styles>{0}", Environment.NewLine);
         sb.AppendFormat(@"  <Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine);
         sb.AppendFormat(@"   <Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine);
         sb.AppendFormat(@"   <Borders/>{0}", Environment.NewLine);
         sb.AppendFormat(@"   <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>{0}", Environment.NewLine);
         sb.AppendFormat(@"   <Interior/>{0}", Environment.NewLine);
         sb.AppendFormat(@"   <NumberFormat/>{0}", Environment.NewLine);
         sb.AppendFormat(@"   <Protection/>{0}", Environment.NewLine);
         sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
         sb.AppendFormat(@"  <Style ss:ID=""s62"">{0}", Environment.NewLine);
         sb.AppendFormat(@"   <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine);
         sb.AppendFormat(@"    ss:Bold=""1""/>{0}", Environment.NewLine);
         sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
         sb.AppendFormat(@"  <Style ss:ID=""s63"">{0}", Environment.NewLine);
         sb.AppendFormat(@"   <NumberFormat ss:Format=""Short Date""/>{0}", Environment.NewLine);
         sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
         sb.AppendFormat(@" </Styles>{0}", Environment.NewLine);
         sb.Append(@"{0}\r\n</Workbook>");
         return sb.ToString();
      }

      private static string replaceXmlChar(string input)
      {
         input = input.Replace("&", "&amp");
         input = input.Replace("<", "&lt;");
         input = input.Replace(">", "&gt;");
         input = input.Replace("\"", "&quot;");
         input = input.Replace("'", "&apos;");
         return input;
      }

      private static string getCell(Type type, object cellData)
      {
         var data = (cellData is DBNull) ? "" : cellData;
         if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", data);
         if (type.Name.Contains("Date") && data.ToString() != string.Empty)
         {
            return string.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));
         }
         return string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(data.ToString()));
      }

      private static string getWorksheets(DataSet source)
      {
         var sw = new StringWriter();
         if (source == null || source.Tables.Count == 0)
         {
            sw.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
            return sw.ToString();
         }
         foreach (DataTable dt in source.Tables)
         {
            if (dt.Rows.Count == 0)
               sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) + "\">\r\n<Table>\r\n<Row><Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
            else
            {
               //write each row data                
               var sheetCount = 0;
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                  if ((i % rowLimit) == 0)
                  {
                     //add close tags for previous sheet of the same data table
                     if ((i / rowLimit) > sheetCount)
                     {
                        sw.Write("\r\n</Table>\r\n</Worksheet>");
                        sheetCount = (i / rowLimit);
                     }
                     sw.Write("\r\n<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) +
                              (((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\">\r\n<Table>");
                     //write column name row
                     sw.Write("\r\n<Row>");
                     foreach (DataColumn dc in dt.Columns)
                        sw.Write(string.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(dc.ColumnName)));
                     sw.Write("</Row>");
                  }
                  sw.Write("\r\n<Row>");
                  foreach (DataColumn dc in dt.Columns)
                     sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));
                  sw.Write("</Row>");
               }
               sw.Write("\r\n</Table>\r\n</Worksheet>");
            }
         }

         return sw.ToString();
      }

      public static string GetExcelXml(DataTable dtInput, string filename)
      {
         var excelTemplate = getWorkbookTemplate();
         var ds = new DataSet();
         ds.Tables.Add(dtInput.Copy());
         var worksheets = getWorksheets(ds);
         var excelXml = string.Format(excelTemplate, worksheets);
         return excelXml;
      }

      public static string GetExcelXml(DataSet dsInput, string filename)
      {
         var excelTemplate = getWorkbookTemplate();
         var worksheets = getWorksheets(dsInput);
         var excelXml = string.Format(excelTemplate, worksheets);
         return excelXml;
      }

      public static void ToExcel(DataSet dsInput, string filename, HttpResponse response)
      {
         var excelXml = GetExcelXml(dsInput, filename);
         response.Clear();
         response.AppendHeader("Content-Type", "application/vnd.ms-excel");
         response.AppendHeader("Content-disposition", "attachment; filename=" + filename);
         response.Write(excelXml);
         response.Flush();
         response.End();
      }

      public static void ToExcel(DataTable dtInput, string filename, HttpResponse response)
      {
         var ds = new DataSet();
         ds.Tables.Add(dtInput.Copy());
         ToExcel(ds, filename, response);
      }

      public static string[] GetConnectionExcel(HttpPostedFile HttpFileUpload,HttpServerUtility ServerHU)
      {
         string[] strExcelConn = new string[2];
         string strFileName = ServerHU.HtmlEncode(HttpFileUpload.FileName);
         string strExtension = Path.GetExtension(strFileName);

         if (strExtension != ".xls" && strExtension != ".xlsx")
         {
            return strExcelConn;
         }

         string strUploadFileName = "~/UploadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;
         strExcelConn[1] = strUploadFileName; 
         HttpFileUpload.SaveAs(ServerHU.MapPath(strUploadFileName));
         
         //if (strExtension == ".xls")
         //   strExcelConn[0] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ServerHU.MapPath(strUploadFileName) + ";Extended Properties='Excel 8.0;HDR=YES;'";
         //else
            strExcelConn[0] = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ServerHU.MapPath(strUploadFileName) + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";

         return strExcelConn;
      }

      public static MemoryStream MergePDFFiles(MemoryStream[] source)
      {

         MemoryStream msDoc = new MemoryStream();
         Document doc = new Document();
         PdfReader reader;
         int intNumPages;
         int intCurPage;

         PdfWriter writer = PdfWriter.GetInstance(doc, msDoc);
         doc.Open();
         PdfContentByte cb = writer.DirectContent;

         foreach (MemoryStream ms in source)
         {
            reader = new PdfReader(ms.ToArray());
            intNumPages = reader.NumberOfPages;
            intCurPage = 0;

            while (intCurPage < intNumPages)
            {
               intCurPage += 1;
               doc.SetPageSize(PageSize.A4);
               doc.NewPage();
               PdfImportedPage page = writer.GetImportedPage(reader, intCurPage);
               int intRot = reader.GetPageRotation(intCurPage);

               if (intRot == 90 || intRot == 270)
                  cb.AddTemplate(page, 0, -1.0F, 1.0F, 0, 0, reader.GetPageSizeWithRotation(intCurPage).Height);
               else
                  cb.AddTemplate(page, 1.0F, 0, 0, 1.0F, 0, 0);
            }
         }

         writer.Flush();
         doc.Close();

         return msDoc;
      }

   }
}
